﻿using NB.MGJ.FrameWork;
using NB.MGJ.Model.HomeIndexViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using NB.MGJ.Model.HomeCount;
using System.Data;
using MySql.Data.MySqlClient;

namespace NB.MGJ.Service.AntiFake
{
    public class AntiFakeQueryCodeLogService
    {
        public BaseRepository _baseRepository = new BaseRepository();

        public int GetAlreadyCreateCodeCount ()
        {

            StringBuilder sb = new StringBuilder();
            sb.Append("select SUM(Gen_Code_Nums) from codemanage_gencode ");
            var sql=sb.ToString();
            var   result=_baseRepository.GetSingle(sql);
            return Convert.ToInt32(result);
        
        }


        public int GetEnterpriseCount()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT count(*) from organization_enterprise ");
            var sql = sb.ToString();

            var result = _baseRepository.GetSingle(sql);
            return Convert.ToInt32(result);
        }


        public int GetAlreadyScanCodeCount(string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();

            sb.Append("select count(*) from "+source );
            var sql = sb.ToString();

            var result = _baseRepository.GetSingle(sql);
            return Convert.ToInt32(result);

        }


        public int GetToDayScanCodeCount (string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();

            sb.Append("select count(*) from " + source + " where to_days(QueryTime) = to_days(now() ) ");
             var sql=sb.ToString();
            var result = _baseRepository.GetSingle(sql);
            return Convert.ToInt32(result);

        }







        public HourParmCountModel GetHourDataByDayAndCode(DateTime date, List<HourParmModel> hpList, string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            var day = date.ToString("yyyy-MM-dd");
            StringBuilder sb = new StringBuilder();

            sb.Append("select ");

            sb.Append("(select Count(*) from " + source + " where QueryTime>'" + day + " " + hpList[0].Start + "' and QueryTime<'" + day + " " + hpList[0].End + "') as One, ");

            sb.Append("(select Count(*) from " + source + " where QueryTime>'" + day + " " + hpList[1].Start + "' and QueryTime<'" + day + " " + hpList[1].End + "') as Two, ");

            sb.Append("(select Count(*) from " + source + " where QueryTime>'" + day + " " + hpList[2].Start + "' and QueryTime<'" + day + " " + hpList[2].End + "') as Three, ");

            sb.Append("(select Count(*) from " + source + " where QueryTime>'" + day + " " + hpList[3].Start + "' and QueryTime<'" + day + " " + hpList[3].End + "') as Four ");

            var sql = sb.ToString();

            return _baseRepository.ExecuteText<HourParmCountModel>(sql);
        }



        public List<CodeLogModel> GetLogByDateAndECode(DateTime dateTime, string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            var strDateTime = dateTime.ToString("yyyy-MM-dd hh:mm");

            StringBuilder sb = new StringBuilder();
            sb.Append("select * from " + source + " where to_days(QueryTime) = to_days(now() ) ");

            var sql = sb.ToString();

            return _baseRepository.ExecuteSqlCollection<CodeLogModel>(sql).ToList();


        }



        public QueryTypeRateModel GetQueryTypeRate(DateTime dateTime, string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();
            sb.Append("select (select count(*) from " + source + " where QueryType=0 and  to_days(QueryTime) = to_days(now())) as one,");
            sb.Append("(select count(*) from " + source + " where QueryType=1 and  to_days(QueryTime) = to_days(now())) as two,");
            sb.Append("(select count(*) from " + source + " where QueryType=2 and  to_days(QueryTime) = to_days(now())) as three,");
            sb.Append("(select count(*) from " + source + " where QueryType=3 and  to_days(QueryTime) = to_days(now())) as four,");
            sb.Append("(select count(*) from " + source + ") as ETcount,");
            sb.Append("(select count(*) from " + source + " where QueryType=0 and IsQueryExceptional=1 and  to_days(QueryTime) = to_days(now())) as oneerror,");
            sb.Append("(select count(*) from " + source + " where QueryType=1 and IsQueryExceptional=1 and  to_days(QueryTime) = to_days(now())) as twoerror,");
            sb.Append("(select count(*) from " + source + " where QueryType=2 and IsQueryExceptional=1 and  to_days(QueryTime) = to_days(now())) as threeerror,");
            sb.Append("(select count(*) from " + source + " where QueryType=3 and IsQueryExceptional=1 and  to_days(QueryTime) = to_days(now())) as fourerror");

            var sql = sb.ToString();
            return _baseRepository.ExecuteText<QueryTypeRateModel>(sql);
        }

        public LastSevenDaysValueModel GetLastSevenDayDataByType(string[] sevenDays, int type, string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            sb.Append("(select count(*) from " + source + " where date_format(QueryTime,'%Y-%m-%d')='" + sevenDays[0] + "' and SearchType=" + type + ") as First ,");
            sb.Append("(select count(*) from " + source + " where date_format(QueryTime,'%Y-%m-%d')='" + sevenDays[1] + "' and SearchType=" + type + ") as Second ,");
            sb.Append("(select count(*) from " + source + " where date_format(QueryTime,'%Y-%m-%d')='" + sevenDays[2] + "' and SearchType=" + type + ") as Thrid ,");
            sb.Append("(select count(*) from " + source + " where date_format(QueryTime,'%Y-%m-%d')='" + sevenDays[3] + "' and SearchType=" + type + ") as Fourth ,");
            sb.Append("(select count(*) from " + source + " where date_format(QueryTime,'%Y-%m-%d')='" + sevenDays[4] + "' and SearchType=" + type + ") as Fifth ,");
            sb.Append("(select count(*) from " + source + " where date_format(QueryTime,'%Y-%m-%d')='" + sevenDays[5] + "' and SearchType=" + type + ") as Sixth ,");
            sb.Append("(select count(*) from " + source + " where date_format(QueryTime,'%Y-%m-%d')='" + sevenDays[6] + "' and SearchType=" + type + ") as Seventh");

            var sql = sb.ToString();

            return _baseRepository.ExecuteText<LastSevenDaysValueModel>(sql);

        }


        public List<CodeLogModel> GetLogByDateAndECode(DateTime dateTime, string code, int pageSize)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            var strDateTime = dateTime.ToString("yyyy-MM-dd hh:mm");

            StringBuilder sb = new StringBuilder();
            //sb.Append("select * from " + source + " where to_days(QueryTime) = to_days(now() ) limit " + pageSize + " ");

            sb.Append("select * from " + source + " where 1=1 limit " + pageSize + " ");
            var sql = sb.ToString();
            return _baseRepository.ExecuteSqlCollection<CodeLogModel>(sql).ToList();
        }


        public List<object> GetRealTimeLogListAdmin_New()
        {
            //SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.codelog0001 
            //UNION ALL
            //SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.codelog00200001 
            //UNION ALL
            //SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.codelog0020
            //UNION ALL
            //SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.codelog00020002
            //UNION ALL
            //SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.codelog00020003
            //UNION ALL
            //SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.codelog00030002
            //ORDER BY QueryTime DESC LIMIT 10
            string ConnectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
            string LogDBName = ConfigurationManager.AppSettings["logDbName"];
            List<object> codelogmodel = new List<object>();
            if (string.IsNullOrWhiteSpace(ConnectionString))
            {
                return codelogmodel;
            }
            try
            {
                List<string> List_TableName = new List<string>();
                //StringBuilder stringBuilder = new StringBuilder();
                using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("SHOW TABLES FROM {0}", LogDBName);
                        MySqlDataReader Reader = cmd.ExecuteReader();
                        while (Reader.Read())
                        {
                            if (Reader[0] != null)
                            {
                                //stringBuilder.Append(string.Format("SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.{0}", Reader[0]));
                                List_TableName.Add(string.Format("SELECT Fw_SCode,QueryTime,QueryProvince,Valid FROM nblogmain.{0}", Reader[0]));
                            }
                        }
                        Reader.Close();
                        // 表过多时 会引起  SQL过长  注意后续修改
                        if (List_TableName.Count > 0)
                        {
                            var SQL_String = string.Join(" UNION ALL ", List_TableName);
                            SQL_String += " ORDER BY QueryTime DESC LIMIT 10 ";
                            cmd.CommandText = SQL_String;
                            Reader = cmd.ExecuteReader();
                            while (Reader.Read())
                            {
                                codelogmodel.Add(new
                                {
                                    QueryTime = Reader["QueryTime"],
                                    Code = Reader["Fw_SCode"],
                                    Province = Reader["QueryProvince"],
                                    Valid = Reader["Valid"].ToString() == "0" ? "异常" : "正常"
                                });
                            }
                            Reader.Close();
                            return codelogmodel;
                        }
                    }
                }
            }
            catch { }
            return codelogmodel;
        }

        public List<CodeLogModel> GetRealTimeLogListAdmin()
        {
            var sql1 = "select table_name from information_schema.tables where table_schema='nblogmain'";
            List<string> list = _baseRepository.ExecuteSqlCollection<string>(sql1).ToList();
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var sb = new StringBuilder();
            sb.Append("select * from (");
            for (int i = 0; i < list.Count; i++)
            {

                sb.Append(" select * from " + dbName + "." + list[i] + " t");

                if (i < list.Count - 1)
                {
                    sb.Append(" union all ");
                }
            }
            sb.Append(" ) a limit 20 ");

            var sql3 = sb.ToString();
            var result = _baseRepository.ExecuteSqlCollection<CodeLogModel>(sql3).ToList();
            return result;
        }



        public List<ProductSearchModel> GetProductSearch(string code, int pageSize)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();
            sb.Append("select a.ProductId,Count(*) as Count,b.ProductName from " + source + " a LEFT JOIN nbsecuritymain.product_product b on a.ProductId=b.ID GROUP BY ProductId ORDER BY count desc limit " + pageSize + " ");

            var sql = sb.ToString();
            return _baseRepository.ExecuteSqlCollection<ProductSearchModel>(sql).ToList();
        }

        public List<NameValueModel> GetProvinceData(string code, int searchType)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();
            sb.Append("select count(*) as value , QueryProvince as name  from " + source + " where QueryProvince in ('北京','天津','上海','重庆','河北','河南','云南','辽宁','黑龙江','湖南','安徽','山东','新疆','江苏','浙江','江西','湖北','广西','甘肃','山西','内蒙古','陕西','吉林','福建','贵州','广东','青海','西藏','四川','宁夏','海南','台湾','香港','澳门') and SearchType=" + searchType + " GROUP BY name");
            var sql = sb.ToString();
            return _baseRepository.ExecuteSqlCollection<NameValueModel>(sql).ToList();
        }

        public HourParmCountModel GetAgeParm(List<HourParmModel> hpList, int type, string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            sb.Append("( select count(*) from " + source + " where Age>=" + hpList[0].Start + " and Age<=" + hpList[0].End + " and SearchType=" + type + ") as One,");
            sb.Append("( select count(*) from " + source + " where Age>=" + hpList[1].Start + " and Age<=" + hpList[1].End + " and SearchType=" + type + "  )as Two,");
            sb.Append("( select count(*) from " + source + " where Age>=" + hpList[2].Start + " and Age<=" + hpList[2].End + " and SearchType=" + type + "  )as Three,");
            sb.Append("( select count(*) from " + source + " where Age>=" + hpList[3].Start + " and Age<=" + hpList[3].End + " and SearchType=" + type + "  )as Four,");
            sb.Append("( select count(*) from " + source + " where Age>=" + hpList[4].Start + " and Age<=" + hpList[4].End + " and SearchType=" + type + "  )as Five");
            var sql = sb.ToString();
            return _baseRepository.ExecuteText<HourParmCountModel>(sql);
        }

        public SexModel GetSexData(int type, string code)
        {
            var dbName = ConfigurationManager.AppSettings["logDbName"];
            var tableNameParm = ConfigurationManager.AppSettings["logTableName"];
            var tableName = "`" + tableNameParm + code + "`";
            var source = dbName + "." + tableName;

            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            sb.Append("(select count(*)  from " + source + " where sex=0 and SearchType=" + type + " ) as 'maleCount',");
            sb.Append("(select count(*)  from " + source + " where sex=1 and SearchType=" + type + " ) as 'femaleCount',");
            sb.Append("(select count(*)  from " + source + " where SearchType=" + type + ") as ETcount ");
            var sql = sb.ToString();

            return _baseRepository.ExecuteText<SexModel>(sql);
        }

        /// <summary>
        /// 新建扫码记录表 
        /// 新建人：huajun 
        /// 时间：2017/12/09
        /// </summary>
        /// <returns></returns>
        public int CreateScanLogTb(string code)
        {
            string sql = " use  `nblogmain`; " +
            "DROP TABLE IF EXISTS `codelog{0}`; " +
    " CREATE TABLE `codelog{0}` (" +
    "  `ID` int(11) NOT NULL AUTO_INCREMENT, " +
    "  `Fw_SCode` varchar(100) NOT NULL DEFAULT '' COMMENT '防伪码', " +
    "  `Fw_Code` varchar(100) DEFAULT NULL COMMENT '服务码', " +
    "  `Wl_Code` varchar(100) DEFAULT NULL COMMENT '物流码', " +
    "  `QueryType` int(11) NOT NULL COMMENT '查询类型 0 微信 1 网站  2 短信 3 电话'," +
    "  `QueryTime` datetime NOT NULL COMMENT '查询时间', " +
    "  `QueryMessage` varchar(100) DEFAULT NULL COMMENT '返回消息', " +
    "  `Valid` int(1) DEFAULT NULL COMMENT '真伪 0 异常 1真', " +
    "  `QueryProvince` varchar(50) DEFAULT NULL COMMENT '查询省份', " +
    "  `QueryCity` varchar(50) DEFAULT NULL, " +
    "  `ProductId` int(11) DEFAULT NULL COMMENT '查询产品id', " +
    "  `EnterpriseId` int(11) DEFAULT NULL COMMENT '机构ID', " +
    "  `Lat` varchar(20) DEFAULT NULL COMMENT '纬度', " +
    "  `Lng` varchar(20) DEFAULT NULL COMMENT '经度', " +
    "  `Address` varchar(200) DEFAULT '' COMMENT '详细地址？？？？查询者所在位置？', " +
    "  `Sex` int(11) DEFAULT '0' COMMENT '0男  1女', " +
    "  `Age` int(11) DEFAULT NULL COMMENT '查询者年龄', " +
    "  `QueryIp` varchar(50) NOT NULL, " +
    "  `SearchType` int(11) DEFAULT NULL COMMENT '扫码类型 0 防伪 1 溯源 2 促销 3 窜货'," +
    "  `QueryValue` varchar(100) DEFAULT NULL COMMENT '查询结果', " +   
    "  `IsQueryExceptional` int(11) DEFAULT NULL, " +
    "  PRIMARY KEY (`ID`) " +
    ") ENGINE=InnoDB AUTO_INCREMENT=144 DEFAULT CHARSET=utf8;";
            sql = string.Format(sql, code);
            return _baseRepository.ExecuteSql<int>(sql);
        }
        //判断表是否存在
        public bool IsExist(string tableName)
        {
            bool result = true;
            string sql = "use nblogmain;SHOW TABLES LIKE '" + tableName + "';";
            var str = _baseRepository.ExecuteText<string>(sql);
            if (string.IsNullOrEmpty(str))result=false;
            return result;
        }
    }
}
